# -*- coding: utf-8 -*-
"""
统计推荐
"""

from collections import Counter
from utils.DataBase import DataBase


class StatisticsRecommender:
    def __init__(self):
        self.db = DataBase()

    def statisticsHistoricalTopRate(self, more_n):
        self.db.delete("TRUNCATE TABLE historical_top_products;")
        # 获取到所有评分数据
        ratings = self.db.select("select * from ratings;")
        productCounter = Counter([rate[2] for rate in ratings])
        # 获取到评论数最多的前more_n个商品的商品productId
        productIdSet = set([term[0] for term in productCounter.most_common(more_n)])
        data = [(p,) for p in productIdSet]
        sql = """
        insert into historical_top_products(product_id) values (%s)
        """
        try:
            self.db.insert_many(sql, data)
            print("historical_top_products数据插入成功")
        except Exception as e:
            print(f"发生错误：{e}")

    def statisticsRecentlyTopRate(self):
        self.db.delete("TRUNCATE TABLE recently_top_products;")
        sql = """
        SELECT product_id,
        COUNT(product_id) AS product_count,
        DATE_FORMAT(rating_time, '%Y-%m')
        FROM ratings 
        GROUP BY  product_id, DATE_FORMAT(rating_time, '%Y-%m')
        ORDER BY DATE_FORMAT(rating_time, '%Y-%m') desc, product_count desc ;
        """
        data = self.db.select(sql)
        sql = "insert into recently_top_products(product_id, product_count, `year_month`) values (%s,%s,%s)"
        try:
            self.db.insert_many(sql, data)
            print("recently_top_products数据插入成功")
        except Exception as e:
            print(f"发生错误：{e}")

    def statisticsAverageProducts(self):
        self.db.delete("TRUNCATE TABLE average_products;")
        sql = """
        select product_id, avg(rating) as product_rating
        from ratings
        group by product_id
        order by product_rating desc;
        """
        data = self.db.select(sql)
        sql = "insert into average_products(product_id, product_rating) values (%s,%s)"
        try:
            self.db.insert_many(sql, data)
            print("average_products数据插入成功")
        except Exception as e:
            print(f"发生错误：{e}")


def main():
    recommender = StatisticsRecommender()
    recommender.statisticsHistoricalTopRate(100)
    recommender.statisticsRecentlyTopRate()
    recommender.statisticsAverageProducts()


if __name__ == '__main__':
    main()
